package com.tcode.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.catalina.User;

import com.tcode.bean.PrivilegeBean;
import com.tcode.bean.RoleBean;
import com.tcode.bean.UserBean;
import com.tcode.utils.JDBCUtil;

public class UserDao {
	
	

	/*
	 * 通过用户名和密码利用sql中的count函数查询该用户是否存在。
	 * */
	public int getUserByUserNameAndPwd(UserBean u) throws Exception{
		int i =0;
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) from sys_user_t where uname = ? and pwd =? and ifvalide =1 ");
		
		
		Connection con = JDBCUtil.getConection();
		
		PreparedStatement ps = con.prepareStatement(sql.toString());
		
		ps.setString(1, u.getName());
		ps.setString(2, u.getPhone());
		
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()){
			i=rs.getInt(1);
		}
		
		JDBCUtil.close(con);
		return  i;
	}
	/*
	 * 通过用户名和密码查询登录用户的基本信息
	 * */
	public UserBean getUserbeanByUserNameAndPwd(UserBean u) throws Exception{
		UserBean user=new UserBean();
		StringBuffer sql = new StringBuffer();
		sql.append("select uid,deptid,uname,pwd , DATE_FORMAT(createdate,'%Y-%m-%d %H:%i:%s') AS createdate from sys_user_t where uname = ? and pwd =? ");
		
		
		Connection con = JDBCUtil.getConection();
		
		PreparedStatement ps = con.prepareStatement(sql.toString());
		
		ps.setString(1, u.getName());
		ps.setString(2, u.getPhone());
		
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()){
			user.setId(rs.getInt(1));
			user.setDeptId(rs.getInt(2));
			user.setName(rs.getNString("uname"));
			user.setPhone(rs.getNString("pwd"));
			user.setCreatedate(rs.getNString("createdate"));
		}
		JDBCUtil.close(con);
		return  user;
	}
	/**
	 * 根据传入的条件对用户记录表做分页查询
	 * */
	public List<UserBean>  getUserList(UserBean u,int indexNo,int pageSize) throws SQLException{
		
		List<UserBean> list = new ArrayList<UserBean>();
		int preNum=0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.uid,t.uname,t.pwd , DATE_FORMAT(t.createdate,'%Y-%m-%d %H:%i:%s') AS createdate,");
		sql.append("(SELECT d.uname FROM sys_dept_t d WHERE d.uid=t.deptid )AS deptname , ifvalide ");
		sql.append(" FROM sys_user_t t where 1=1 ");
		if(u.getName()!=null&&!("".equals(u.getName()))){
			sql.append(" and uname = ? ");
			preNum++;
		}
		sql.append("order by createdate desc limit ? ,?  ");
		Connection con = JDBCUtil.getConection();
		System.out.println(sql.toString()+","+indexNo+","+pageSize);
		PreparedStatement ps = con.prepareStatement(sql.toString());
		if(preNum!=0){
			ps.setString(1, u.getName());
		}else{
			ps.setInt(1, indexNo);
			ps.setInt(2, pageSize);
		}
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) {
			UserBean user = new UserBean();
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setPhone(rs.getString(3));
			user.setCreatedate(rs.getString(4));
			user.setDeptName(rs.getNString("deptname"));
			int val =rs.getInt(6);
			user.setValideDesc(val==1?"有效":"失效");
			user.setIfValide(val);
			list.add(user);
		}
		JDBCUtil.close(con);
		return list;
		
	}
	/**
	 * 查询用户表中所有的用户
	 * */
	public List<UserBean> searchAllUser() throws SQLException{
		List<UserBean> list = new ArrayList<UserBean>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT uid,uname,pwd , DATE_FORMAT(createdate,'%Y-%m-%d %H:%i:%s') AS createdate ,");
		sql.append("(SELECT d.uname FROM sys_dept_t d WHERE d.uid=t.deptid )AS deptname ");
		sql.append("FROM sys_user_t where 1=1 ");
		
		
		Connection con = JDBCUtil.getConection();
		PreparedStatement ps = con.prepareStatement(sql.toString());
		
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) {
			UserBean user = new UserBean();
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setPhone(rs.getString(3));
			user.setCreatedate(rs.getString(4));
			list.add(user);
		}
		JDBCUtil.close(con);
		return list;
	}
	
	/**
	 * 根据条件查询用户表中，适合条件用户的数量
	 * */
	public int getUserCountBySomething(UserBean u) throws Exception{
		
		int i =0;
		int preNum=0;
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) from sys_user_t where 1=1 ");
		if(u.getName()!=null&&!("".equals(u.getName()))){
			sql.append(" and uname = ? ");
			preNum++;
		}
		
		
		Connection con = JDBCUtil.getConection();
		
		PreparedStatement ps = con.prepareStatement(sql.toString());
		if(preNum!=0){
			ps.setString(1, u.getName());
		}
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()){
			i=rs.getInt(1);
		}
		JDBCUtil.close(con);
		return  i;
	}
	
	/**
	 * 为用户表增加一条用户信息记录
	 * */
	public int insertUser(UserBean user) throws SQLException{
		int flag = 0;
		
		StringBuffer sql =new StringBuffer();
		
		sql.append("insert into sys_user_t (uname,pwd,deptid) values (?,?,1) ");
		
		Connection con=  JDBCUtil.getConection();
		
		PreparedStatement ps =con.prepareStatement(sql.toString());
		
		ps.setString(1, user.getName());
		ps.setString(2, user.getPhone());
		
		flag = ps.executeUpdate();
		JDBCUtil.close(con);
		return flag;
	}
	/**
	 * 根据ID更新用户信息
	 * */
	public int modUser(UserBean user)throws Exception{
		int flag = 0;
		
		StringBuffer sql =new StringBuffer();
		
		sql.append("update sys_user_t set pwd = ? ,deptid=?  where uid = ? ");
		
		Connection con=  JDBCUtil.getConection();
		
		PreparedStatement ps =con.prepareStatement(sql.toString());
		
		ps.setString(1, user.getPhone());
		ps.setInt(2, user.getDeptId());
		ps.setInt(3, user.getId());
		
		flag = ps.executeUpdate();
		JDBCUtil.close(con);
		return flag;
	}
	/**
	 * 查询用户表中所有的用户
	 * */
	public List<UserBean> getAll() throws SQLException{
		List<UserBean> list = new ArrayList<UserBean>();
		
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT uid,uname,pwd , DATE_FORMAT(createdate,'%Y-%m-%d %H:%i:%s') AS createdate "
				+ "FROM sys_user_t where 1=1 ");
		
		Connection conn= JDBCUtil.getConection();
		
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) {
			UserBean u =new UserBean();
			u.setId(rs.getInt(1));
			u.setName(rs.getString(2));
			u.setPhone(rs.getString(3));
			u.setCreatedate(rs.getNString("createdate"));
			list.add(u);
		}
		JDBCUtil.close(conn);
		return list;
	}
	/**
	 * 
	 * */
	public List<PrivilegeBean> getPrivilengeByRoleId(int id) throws SQLException{
		List<PrivilegeBean> list = new ArrayList<PrivilegeBean>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.id,t.privilege_name,t.privilege_path,t.p_type,t.parentid FROM sys_privilege_t  t ");
		sql.append("LEFT JOIN  sys_role_privilege_t rt ") ;
		sql.append("ON t.id= rt.privilege_id " ); 
		sql.append("WHERE rt.role_id =?");
		Connection conn = JDBCUtil.getConection();
		
		PreparedStatement ps =  conn.prepareStatement(sql.toString());
		ps.setInt(1, id);  
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()) {
			PrivilegeBean p =new PrivilegeBean();
			p.setId(rs.getInt(1));
			p.setParentName(rs.getString(2));
			p.setPrivilegePath(rs.getString(3));
			
			p.setParentId(rs.getString(5));
			list.add(p);
		}
		JDBCUtil.close(conn);
		return list;
	}
	/**
	 * 
	 * */
	public List<RoleBean> getRoleByUserId(int id) throws SQLException{
		List<RoleBean> list = new ArrayList<RoleBean>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.id,t.rolename FROM sys_role_t t LEFT JOIN sys_user_role_t ur ");
		sql.append("ON t.id=ur.roleid WHERE ur.userid =? ") ;
		
		Connection conn = JDBCUtil.getConection();
		
		PreparedStatement ps =  conn.prepareStatement(sql.toString());
		ps.setInt(1, id);  
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()) {
			RoleBean r =new RoleBean();
			r.setId(rs.getInt(1));
			r.setRoleName(rs.getString(2));
			list.add(r);
		}
		JDBCUtil.close(conn);
		return list;
	}
	/**
	 * 获取所有角色
	 * */
	public List<RoleBean> getAllRole() throws SQLException{
		List<RoleBean> list = new ArrayList<RoleBean>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.id,t.rolename FROM sys_role_t t ") ;
		
		Connection conn = JDBCUtil.getConection();
		
		PreparedStatement ps =  conn.prepareStatement(sql.toString());
		
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()) {
			RoleBean r =new RoleBean();
			r.setId(rs.getInt(1));
			r.setRoleName(rs.getString(2));
			list.add(r);
		}
		JDBCUtil.close(conn);
		return list;
	}
	/**
	 * 获取所有权限
	 * */
	public List<PrivilegeBean> getAllPrivilenge() throws SQLException{
		List<PrivilegeBean> list = new ArrayList<PrivilegeBean>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.id,t.privilege_name,t.privilege_path,t.p_type,t.parentid FROM sys_privilege_t  t ");
		
		Connection conn = JDBCUtil.getConection();
		
		PreparedStatement ps =  conn.prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()) {
			PrivilegeBean p =new PrivilegeBean();
			p.setId(rs.getInt(1));
			p.setParentName(rs.getString(2));
			p.setPrivilegePath(rs.getString(3));
			p.setParentId(rs.getString(5));
			list.add(p);
		}
		JDBCUtil.close(conn);
		return list;
	}
	public int addUserRole(RoleBean role) throws SQLException {
		StringBuffer sql = new StringBuffer("");
		sql.append("insert into sys_user_role_t(roleid,userid) values(?,?)");
		Connection conn = JDBCUtil.getConection();
		
		PreparedStatement ps =  conn.prepareStatement(sql.toString());
		ps.setString(1, String.valueOf(role.getId()));
		ps.setString(2, String.valueOf(role.getUserid()));
		int i =ps.executeUpdate();
		JDBCUtil.close(conn);
		return i;
	}
	
	public int delUserRole(RoleBean role ) throws SQLException {
		StringBuffer sql = new StringBuffer("");
		sql.append("delete from sys_user_role_t where roleid = ? and userid = ?");
		Connection conn = JDBCUtil.getConection();
		
		PreparedStatement ps =  conn.prepareStatement(sql.toString());
		ps.setString(1, String.valueOf(role.getId()));
		ps.setString(2, String.valueOf(role.getUserid()));
		int i =ps.executeUpdate();
		JDBCUtil.close(conn);
		return i;
	}
	
	

}
